﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace prjDALHRM
{
    public class clsDatabase
    {
        //private string strConectionString = "server=.; database=5mTech_HRM; User ID=sa; Pwd=121212; Connect Timeout=40;";
        private string strConectionString = @"Data Source=PHU-THUY-PC\THUYENLADY ;database=5mTech_HRM;Integrated Security=True;Connect Timeout=30;";
        public string StrConectionString
        {
            get
            {
                return strConectionString;
            }
            set
            {
                strConectionString = value;
            }
        }
        SqlConnection cn;
        SqlDataAdapter da;
        SqlCommand cmd;

        #region Connection
        /// <summary>
        /// Set Connection String before Connect
        /// </summary>
        protected void Connect()
        {
            try
            {
                cn = new SqlConnection(strConectionString);
                cn.Open();
            }
            catch (Exception er)
            {
                MessageBox.Show(er.Message);
            }

        }

        protected void Connect(string strCnn)
        {
            try
            {
                cn = new SqlConnection(strCnn);
                cn.Open();
            }
            catch (Exception er)
            {
                MessageBox.Show(er.Message);
            }

        }

        protected void Connect(string strServer, string strDB, string strUser, string strPwd, int iTime)
        {
            try
            {
                strConectionString = "Server=" + strServer + "; Database=" + strDB + ";UID=" + strUser + ";PWD=" + strPwd + ";Connect Timeout=" + iTime.ToString();
                cn = new SqlConnection(strConectionString);
                cn.Open();
            }
            catch (Exception er)
            {
                MessageBox.Show(er.Message);
            }
        }

        protected void Connect(string strServer, string strDB)
        {
            try
            {
                strConectionString = "Server=" + strServer + "; Database=" + strDB + ";Intergrated Security=SSPI";
                cn = new SqlConnection(strConectionString);
                cn.Open();
            }
            catch (Exception er)
            {
                MessageBox.Show(er.Message);
            }
        }

        protected void Disconnect()
        {
            try
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
            catch (Exception er)
            {
                MessageBox.Show(er.Message);
            }

        }
        #endregion Connection

        #region "Compline query"
        protected void ThucThiSQL(string strSql)
        {
            try
            {
                Connect();
                cmd = new SqlCommand(strSql, cn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception er)
            {
                throw er;
              
            }
            finally
            {
                Disconnect();
            }
        }

        protected void ThucThiSQL(SqlCommand cmdSql)
        {
            try
            {
                Connect();
                cmd = cmdSql;
                cmd.Connection = cn;
                cmd.ExecuteNonQuery();
            }
            catch (Exception er)
            {
                throw er;
                
            }
            finally
            {
                Disconnect();
            }
        }

        protected void ThucThiSQL(string strSql, List<SqlParameter> lstPara)
        {
            try
            {
                Connect();
                cmd = new SqlCommand(strSql, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                if (lstPara.Count > 0)
                {
                    foreach (SqlParameter para in lstPara)
                    {
                        cmd.Parameters.Add(para);
                    }
                }
                cmd.ExecuteNonQuery();
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                Disconnect();
            }
        }

        protected DataTable GetTable(string strSql)
        {
            DataTable tbl = new DataTable();
            try
            {
                Connect();
                cmd = new SqlCommand(strSql, cn);
                da = new SqlDataAdapter(cmd);
                da.Fill(tbl);
            }
            catch (Exception er)
            {
                MessageBox.Show(er.Message);
            }
            finally
            {
                Disconnect();
            }
            return tbl;
        }

        protected DataTable GetTable(string strTenBang, List<string> lstCot)
        {
            DataTable tbl = new DataTable();
            string strSql = "Select ";
            try
            {
                Connect();
                for (int i = 0; i < lstCot.Count - 1; i++)
                {
                    strSql += lstCot[i] + ",";
                }
                strSql += lstCot[lstCot.Count - 1] + " from " + strTenBang;
                cmd = new SqlCommand(strSql, cn);
                da = new SqlDataAdapter(cmd);
                da.Fill(tbl);
            }
            catch (Exception er)
            {
                MessageBox.Show(er.Message);
            }
            finally
            {
                Disconnect();
            }
            return tbl;
        }

        protected DataTable GetTable(string strTenBang, string[] arCot)
        {
            DataTable tbl = new DataTable();
            string strSql = "Select ";
            try
            {
                Connect();
                for (int i = 0; i < arCot.Length; i++)
                {
                    strSql += arCot[i] + ",";
                }
                strSql += arCot[arCot.Length - 1] + " from " + strTenBang;
                cmd = new SqlCommand(strSql, cn);
                da = new SqlDataAdapter(cmd);
                da.Fill(tbl);
            }
            catch (Exception er)
            {
                MessageBox.Show(er.Message);
            }
            finally
            {
                Disconnect();
            }
            return tbl;
        }

        protected DataTable GetTable(SqlCommand cmdSql)
        {
            DataTable tbl = new DataTable();
            try
            {
                Connect();
                cmd = cmdSql;
                cmd.Connection = cn;
                da = new SqlDataAdapter(cmd);
                da.Fill(tbl);
            }
            catch (Exception er)
            {
                MessageBox.Show(er.Message);
            }
            finally
            {
                Disconnect();
            }
            return tbl;
        }

        protected DataTable ThucThiSQLTraVeBang(string strSql)
        {
            DataTable tbl = new DataTable();
            try
            {
                Connect();
                cmd = new SqlCommand(strSql, cn);
                da = new SqlDataAdapter(cmd);
                da.Fill(tbl);
            }
            catch (Exception er)
            {
                MessageBox.Show(er.Message);
            }
            finally
            {
                Disconnect();
            }
            return tbl;
        }

        protected DataTable ThucThiSQLTraVeBang(SqlCommand cmdSql)
        {
            DataTable tbl = new DataTable();
            try
            {
                Connect();
                cmd = cmdSql;
                cmd.Connection = cn;
                da = new SqlDataAdapter(cmd);
                da.Fill(tbl);
            }
            catch (Exception er)
            {
                MessageBox.Show(er.Message);
            }
            finally
            {
                Disconnect();
            }
            return tbl;
        }

        protected DataTable ThucThiSQLTraVeBang(string strSql, List<SqlParameter> lstPara)
        {
            DataTable tbl = new DataTable();
            try
            {
                Connect();
                cmd = new SqlCommand(strSql, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                if (lstPara.Count > 0)
                {
                    foreach (SqlParameter para in lstPara)
                    {
                        cmd.Parameters.Add(para);
                    }
                }
                da = new SqlDataAdapter(cmd);
                da.Fill(tbl);
            }
            catch (Exception er)
            {
                MessageBox.Show(er.Message);
            }
            finally
            {
                Disconnect();
            }
            return tbl;
        }
        #endregion "Compline query"
    }
}
